package com.rockontrol.ele.service.impl;

import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import org.apache.log4j.Logger;

import com.rksp.util.DatabaseUtil;
import com.rksp.util.StringUtil;
import com.rockontrol.ele.pojo.Ma劳保台帐;
import com.rockontrol.ele.pojo.Ma劳保台帐明细;
import com.rockontrol.ele.pojo.Ma劳保策略;
import com.rockontrol.ele.pojo.Ma帐套;
import com.rockontrol.ele.pojo.Ma物资库存;
import com.rockontrol.ele.pojo.Ma物资计划;
import com.rockontrol.ele.pojo.Ma物资计划明细;
import com.rockontrol.ele.service.MaPlanSvc;
import com.rockontrol.ele.service.model.dbtransaction.MaPlanTrans;
import com.rockontrol.ele.service.skeleton.MaPlanSvcSkeleton;
import com.rockontrol.ele.vo.LabourProtectionStrategyCdt;
import com.rockontrol.ele.vo.MaLabourCdt;
import com.rockontrol.ele.vo.MaPurchaseOrderCdt;
import com.rockontrol.ele.vo.MaPurchaseOrderVo;
import com.rockontrol.ele.vo.MaterialPlanSummarizingCdt;
import com.rockontrol.ele.vo.PurchaseMaterialPlanCdt;

public class MaPlanSvcImpl extends MaPlanSvcSkeleton implements MaPlanSvc{
	private static Logger log = Logger.getLogger(MaEquipmentSvcImpl.class);
	private MaPlanTrans maPlanTrans;
	
	
    public String createThePurchaseMaterialPlan( Ma物资计划 pmp_) {
        try {
            return maPlanTrans.serviceCreateThePurchaseMaterialPlan(pmp_);
        } catch (Exception e) {
            log.error(e.getMessage());
            e.printStackTrace();
            return null;
        }
	}

	public Boolean deldeteThePurchaseMaterialPlan(String oid_) {
	    try {
		return maPlanTrans.serviceDeldeteThePurchaseMaterialPlan(oid_);
	    } catch (Exception e) {
            log.error(e.getMessage());
            e.printStackTrace();
            return false;
        }
	}

	public List<Ma物资计划明细> getDepartmentMaterialPlanDetails(PurchaseMaterialPlanCdt mpc_) {
		List<Ma物资计划明细> mx=new ArrayList<Ma物资计划明细>();
		try {
		    StringBuffer sql=new StringBuffer();
		    String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
		    if("SQLSERVER".equals(dataBaseType)){
		            //数据库为sqlserver时执行的操作
		        sql.append("select convert(varchar(32), q.规格型号ID) 规格型号id,q.规格流水号,q.物资分类," +
		                "convert(varchar(32)," +
		                " q.物资分类ID) 物资分类id,q.单位,q.计划单价,q.分类编码,q.领用状态,q.明细类型,q.厂商名称,convert(varchar(32), q.厂商ID) 厂商id,q.录入时间,q.采购权限,sum(q.批准数量) 申请数量," +
		                "sum(q.申请数量) 批准数量,sum(q.上月余量) 上月余量,q.金额 金额,q.规格型号,q.录入人,convert(varchar(32), q.录入人ID) 录入人id, max(b.单价) 采购单价,sum(q.未购数量) 未购数量," +
		                "max(q.用途) 用途,max(技术参数) 技术参数,max(q.备注) 备注  from MA_物资计划 p left join MA_物资计划明细 q on q.采购计划ID=p.OID left join BA_规格型号  as b on q.规格型号ID=b.OID where 1=1 ");
		    }else if("ORACLE".equals(dataBaseType)){
		            //数据库为oracle时执行的操作
		        sql.append("select to_char( q.规格型号ID) \"规格型号id\",q.规格流水号,q.规格型号,q.物资分类," +
		                "to_char(" +
		                " q.物资分类ID) \"物资分类id\",q.分类编码,q.明细类型,q.领用状态,q.录入人,to_char( q.录入人ID) \"录入人id\",q.单位,q.计划单价,q.录入时间,q.采购权限,sum(q.批准数量) 申请数量," +
		                "sum(q.申请数量) 批准数量,sum(q.上月余量) 上月余量,q.金额 金额,q.厂商名称,to_char( q.厂商ID) \"厂商id\", max(b.单价) 采购单价,sum(q.未购数量) 未购数量," +
		                "max(q.用途) 用途,max(技术参数) 技术参数,max(q.备注) 备注  from MA_物资计划 p left join MA_物资计划明细 q on q.采购计划ID=p.OID left join BA_规格型号  b on q.规格型号ID=b.OID where 1=1 ");
		    }
			if(mpc_!=null){
				if(!StringUtil.isEmpty(mpc_.getType())){
					sql.append(" and p.分类='"+mpc_.getType()+"'");
				}
				if(!StringUtil.isEmpty(mpc_.getState())){
					sql.append(" and p.审批状态='"+mpc_.getState()+"'");
				}
				if(!StringUtil.isEmpty(mpc_.getUnitId())){
					sql.append(" and p.申请单位id='"+mpc_.getUnitId()+"'");
				}
				if(!StringUtil.isEmpty(mpc_.getCollect())){
					sql.append(" and p.生成汇总='"+mpc_.getCollect()+"'");
				}
				if(!StringUtil.isEmpty(mpc_.getMaType())){
					sql.append(" and p.类型分类名='"+mpc_.getMaType()+"'");
				}
				if(!StringUtil.isEmpty(mpc_.getMaterialType())){
				    sql.append(" and p.物资分类 like '%"+mpc_.getMaterialType()+"%'");
				}
				if(!StringUtil.isEmpty(mpc_.getUseState())){
				    sql.append(" and q.领用状态 = '"+mpc_.getUseState()+"'");
				}
				if(!StringUtil.isEmpty(mpc_.getPurState())){
					sql.append(" and q.购买状态 = '"+mpc_.getPurState()+"'");
				}
				if(!StringUtil.isEmpty(mpc_.getStart())){
				    //时间比较
				    if("SQLSERVER".equals(dataBaseType)){
				            //数据库为sqlserver时执行的操作
				        sql.append(" and datediff(m,q.录入时间,'"+mpc_.getStart()+"')=0");
				    }else if("ORACLE".equals(dataBaseType)){
				            //数据库为oracle时执行的操作
				        SimpleDateFormat  sdf = new SimpleDateFormat("yyyy-MM-dd");
	                    Date date = sdf.parse(mpc_.getStart());
	                    java.sql.Date date1 = new java.sql.Date(date.getTime());
	                    sql.append(" and months_between(trunc(to_date(to_char(q.录入时间,'yyyy-MM-dd'),'yyyy-mm-dd'),'mm')," +
	                    		"trunc(to_date('"+date1+"','yyyy-mm-dd'),'mm')" +
	                    		")=0");
				    }
				}
			}
			sql.append(" group by q.规格型号ID,q.规格流水号,q.规格型号,q.物资分类,q.物资分类ID,q.采购权限,q.分类编码,q.单位,q.计划单价,q.录入时间,q.录入人" +
					",q.录入人ID,q.领用状态,q.明细类型,q.厂商名称, q.厂商ID,q.金额");
//			Ma物资计划 jh=new Ma物资计划();
//			jh=(Ma物资计划)service.findObjsHQL(sql.toString()).get(0);
//			String hql="from Ma物资计划明细 where 采购计划id='"+jh.getOid()+"' and 领用状态='"+mpc_.getUseState()+"'";
			 mx=service.findSQLAlias(sql.toString(), Ma物资计划明细.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return mx;
	}

	public Ma物资计划 getLastPlan(String unit, String date) {
		try{
		    String hqlString="from Ma物资计划 ma where ma.分类='需求计划' and ma.申请单位='"+unit+"' and ma.申请时间='"+date+"'";
	        List<Ma物资计划> planList=service.findObjsHQL(hqlString);
	        return planList.isEmpty()?null:planList.get(0);
        }catch(Exception e){
            log.error(e.getMessage());
            e.printStackTrace();
    		return null;
        }
	}
	private String sql_GetPurchaseMaterialPlans(PurchaseMaterialPlanCdt cdt_){
	    String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
	    StringBuffer hql=new StringBuffer("from Ma物资计划 p where 1=1 ");
	    try{
        if(cdt_!=null){
            if(!StringUtil.isEmpty(cdt_.getType())){
                hql.append(" and p.分类 = '"+cdt_.getType()+"' ");
            }
            if(!StringUtil.isEmpty(cdt_.getMaterialType())){
                hql.append(" and p.类型分类名 = '"+cdt_.getMaterialType()+"' ");
            }
            if(!StringUtil.isEmpty(cdt_.getUnit())){
                hql.append(" and p.申请单位 like '%"+cdt_.getUnit()+"%' ");
            }
            if(!StringUtil.isEmpty(cdt_.getState())){
                hql.append(" and p.审批状态 = '"+cdt_.getState()+"' ");
            }
            if(!StringUtil.isEmpty(cdt_.getStart())){
                if("SQLSERVER".equals(dataBaseType)){
                    //数据库为sqlserver时执行的操作
                    hql.append(" and p.录入时间 >= '"+cdt_.getStart()+" 00:00:00' ");
                }else if("ORACLE".equals(dataBaseType)){
                        //数据库为oracle时执行的操作
                    hql.append(" and p.录入时间 >= to_date('"+cdt_.getStart()+" 00:00:00','yyyy-mm-dd hh24:mi:ss') ");
                }
                
            }
            if(!StringUtil.isEmpty(cdt_.getEnd())){
                if("SQLSERVER".equals(dataBaseType)){
                    //数据库为sqlserver时执行的操作
                    hql.append(" and p.录入时间 <= '"+cdt_.getEnd()+" 23:59:59' ");
                }else if("ORACLE".equals(dataBaseType)){
                        //数据库为oracle时执行的操作
                    hql.append(" and p.录入时间<= to_date('"+cdt_.getEnd()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
                }
            }
            if(!StringUtil.isEmpty(cdt_.getCollect())){
                hql.append(" and p.生成汇总 = '"+cdt_.getCollect()+"' ");
            }
            if(!StringUtil.isEmpty(cdt_.getDocNumber())){
                hql.append(" and p.单据编号 like '%"+cdt_.getDocNumber()+"%' ");
            }
            if(!StringUtil.isEmpty(cdt_.getApplyTimeMin())){
                if("SQLSERVER".equals(dataBaseType)){
                    //数据库为sqlserver时执行的操作
                    hql.append(" and p.申请时间 >= '"+cdt_.getApplyTimeMin()+" 00:00:00' ");
                }else if("ORACLE".equals(dataBaseType)){
                        //数据库为oracle时执行的操作
                    hql.append(" and p.申请时间 >=to_date( '"+cdt_.getApplyTimeMin()+" 00:00:00','yyyy-mm-dd hh24:mi:ss') ");
                }
            }
            if(!StringUtil.isEmpty(cdt_.getApplyTimeMax())){
                if("SQLSERVER".equals(dataBaseType)){
                    //数据库为sqlserver时执行的操作
                    hql.append(" and p.申请时间 <= '"+cdt_.getApplyTimeMax()+"' ");
                }else if("ORACLE".equals(dataBaseType)){
                        //数据库为oracle时执行的操作
                    hql.append(" and p.申请时间 <=to_date( '"+cdt_.getApplyTimeMax()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
                }

                
            }
        }
        hql.append(" order by p.录入时间 desc ");
        
            
        }catch(Exception e){
            log.error(e.getMessage());
            e.printStackTrace();
        }
        return hql.toString();
	}
	public List<Ma物资计划> getPurchaseMaterialPlans(PurchaseMaterialPlanCdt cdt_) {
		try{
			return (List<Ma物资计划>)service.findObjsHQL(sql_GetPurchaseMaterialPlans(cdt_));
		}catch (Exception e) {
			log.error(e.getMessage());
			e.printStackTrace();
			return null;
		}
	}

	public String pageInitGetPurchaseMaterialPlans(
			PurchaseMaterialPlanCdt cdt_, String roll_) {
		try {
			return pageFind(roll_, sql_GetPurchaseMaterialPlans(cdt_));
		} catch (Exception e) {
			log.error(e.getMessage());
			e.printStackTrace();
			return null;
		}
	}
	
	public String pageInitPuchaseMaterialPlans(PurchaseMaterialPlanCdt cdt_, String roll_){
	    try {
            return pageFind(roll_, sql_PurchaseMaterialPlans(cdt_));
        } catch (Exception e) {
            log.error(e.getMessage());
            e.printStackTrace();
            return null;
        }
	}
	
	private String sql_PurchaseMaterialPlans(PurchaseMaterialPlanCdt cdt_){
	    String hql = null;
        try {
            StringBuffer sql=new StringBuffer("from Ma物资计划  p where 1=1 ");
            if(cdt_!=null){
                if(!StringUtil.isEmpty(cdt_.getType())){
                    sql.append(" and p.分类='"+cdt_.getType()+"'");
                }
                if(!StringUtil.isEmpty(cdt_.getState())){
                    sql.append(" and p.审批状态='"+cdt_.getState()+"'");
                }
                if(!StringUtil.isEmpty(cdt_.getUnitId())){
                    sql.append(" and p.申请单位ID='"+cdt_.getUnitId()+"'");
                }
                if(!StringUtil.isEmpty(cdt_.getCollect())){
                    sql.append(" and p.生成汇总='"+cdt_.getCollect()+"'");
                }
                if(!StringUtil.isEmpty(cdt_.getMaType())){
                    sql.append(" and p.类型分类名='"+cdt_.getMaType()+"'");
                }
                if(!StringUtil.isEmpty(cdt_.getMaterialType())){
                    sql.append(" and p.物资分类 like '%"+cdt_.getMaterialType()+"%'");
                }
                
                
            }
            Ma物资计划 jh=new Ma物资计划();
            jh=(Ma物资计划)service.findObjsHQL(sql.toString()).get(0);
            hql="from Ma物资计划明细 where 采购计划id='"+jh.getOid()+"' and 领用状态='"+cdt_.getUseState()+"'";
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return hql;
	}

	public Ma物资计划 getThePurchseMaterialPlan(String oid_, String employeeid_,
			String callType) {
	    Ma物资计划 jh=new Ma物资计划();
	    List<Ma物资计划明细> mxlist=new ArrayList<Ma物资计划明细>();
	    String hql="from Ma物资计划 p where p.oid='"+oid_+"'";
	    if(!StringUtil.isEmpty(callType))
	    	hql+=" and p.类型分类 = '"+callType+"'";
        String hql1="from Ma物资计划明细 m where m.采购计划id='"+oid_+"'";
        mxlist=(List<Ma物资计划明细>)service.findObjsHQL(hql1);
        jh=(Ma物资计划)service.findObj(Ma物资计划.class, oid_);
        jh.setPlanDetail(mxlist);
		
		return jh;
	}

	public String summaryPlan(MaterialPlanSummarizingCdt cdt_) {
		try {
			return maPlanTrans.serviceSummaryPlan(cdt_);
		} catch (Exception e) {
			log.error(e.getMessage());
			e.printStackTrace();
			return null;
		}
	}

	public Boolean updateThePurchaseMaterialPlan(Ma物资计划 pmp_, String employeeid) {
	   return  maPlanTrans.serviceUpdateThePurchaseMaterialPlan(pmp_, employeeid);
	}

	public String pageNumMaPlan(String roll_, Integer num_) {
		try {
			return super.pageNum(roll_, num_);
		} catch (Exception e) {
			log.error(e.getMessage());
			e.printStackTrace();
			return null;
		}
	}

	public MaPlanTrans getMaPlanTrans() {
		return maPlanTrans;
	}

	public void setMaPlanTrans(MaPlanTrans maPlanTrans) {
		this.maPlanTrans = maPlanTrans;
	}

    public Ma物资计划 getThePurchseMaterialPlanByBindid(String bindid){
        try{
            String hqlString="from Ma物资计划 a where a.bindId='"+bindid+"'";
            Ma物资计划 planMa=(Ma物资计划)service.findObjsHQL(hqlString).get(0);
            List<Ma物资计划明细> mxlist=(List<Ma物资计划明细>)service.findObjsHQL("from Ma物资计划明细 m where m.采购计划id='"+planMa.getOid()+"'");
            planMa.setPlanDetail(mxlist);
            return planMa;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
        
    }
    
    public String pageInitLabourProtectionStrategy(
    		LabourProtectionStrategyCdt cdt_, String roll_){
    	try{
    		return pageFind(roll_, sql_GetLabourProtectionStrategy(cdt_));
    	} catch (Exception e) {
    		log.error(e.getMessage());
			e.printStackTrace();
			return null;
		}
    }

    public String sql_GetLabourProtectionStrategy(LabourProtectionStrategyCdt cdt_) {
		StringBuffer hql=new StringBuffer("from Ma劳保策略  L where 1=1 ");
	    try{
	    	if(cdt_!=null){
	            if(!StringUtil.isEmpty(cdt_.get工种名称())){
	                hql.append(" and L.工种名称  = '"+cdt_.get工种名称()+"' ");
	            }
	            if(!StringUtil.isEmpty(cdt_.get物资分类())){
	                hql.append(" and L.物资分类 = '"+cdt_.get物资分类()+"' ");
	            }
            }
        }catch(Exception e){
            log.error(e.getMessage());
            e.printStackTrace();
        }
        return hql.toString();
	}

    public String createTheLabourProtectionStrategy(Ma劳保策略 lps_){
    	try {
			service.addObj(lps_);
			return lps_.getOid();
		}
		catch (Exception e) {
			log.error(e.getMessage(), e);
			e.printStackTrace();
			return null;
		}
    }
    public Ma劳保策略 getTheLabourProtectionStrategy(String oid_){
    	try {
			return (Ma劳保策略)service.findObj(Ma劳保策略.class, oid_);
		}
		catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
    }
	
	public Boolean deleteTheLabourProtectionStrategy(String oid_){
		 try {
			service.removeObj(Ma劳保策略.class, oid_);
			return true;
		}catch (Exception e) {
			log.error(e.getMessage(), e);
			return false;
		}
	}
	
	public Boolean updateTheLabourProtectionStrategy(Ma劳保策略 lps_){
		try {
			service.modifyObj(lps_);
			return true;
		}
		catch (Exception e) {
			log.error(e.getMessage(), e);
			return false;
		}
	}

    public Boolean createTheLabourProtection(Ma劳保台帐 pmp){
        try {
            return maPlanTrans.serviceCreateTheLabourProtection(pmp);
            } catch (Exception e) {
                log.error(e.getMessage());
                e.printStackTrace();
                return false;
            }
    }

    public String findTheLabourProtectionSQL(MaLabourCdt cdt){
        String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
        StringBuffer sqlString=new StringBuffer("from Ma劳保台帐  a where 1=1 ");
        if(cdt!=null){
            if(!StringUtil.isEmpty(cdt.get发放单位())){
                sqlString.append(" and a.发放单位 like '%"+cdt.get发放单位()+"%'");
            }
            if(!StringUtil.isEmpty(cdt.get是否发放())){
                sqlString.append(" and a.是否发放 = '"+cdt.get是否发放()+"'");
            }
            if(!StringUtil.isEmpty(cdt.get领用单位())){
                sqlString.append(" and a.领用单位 like '%"+cdt.get领用单位()+"%'");
            }
            if(!StringUtil.isEmpty(cdt.get发放开始日期())){
                if("SQLSERVER".equals(dataBaseType)){
                    //数据库为sqlserver时执行的操作
                    sqlString.append(" and a.发放日期 > '"+cdt.get发放开始日期()+"'");
                }else if("ORACLE".equals(dataBaseType)){
                        //数据库为oracle时执行的操作
                    sqlString.append(" and a.发放日期 > to_date('"+cdt.get发放开始日期()+" 00:00:00','yyyy-mm-dd hh24:mi:ss')");
                }
            }
            if(!StringUtil.isEmpty(cdt.get发放结束日期())){
                if("SQLSERVER".equals(dataBaseType)){
                    //数据库为sqlserver时执行的操作
                    sqlString.append(" and a.发放日期 < '"+cdt.get发放结束日期()+"'");
                }else if("ORACLE".equals(dataBaseType)){
                        //数据库为oracle时执行的操作
                    sqlString.append(" and a.发放日期 <to_date( '"+cdt.get发放结束日期()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
                }
            }
        }
        sqlString.append("order by a.发放日期 desc");
        return sqlString.toString();
    }
    public List<Ma劳保台帐> findTheLabourProtection(MaLabourCdt cdt){
        try{
            return service.findObjsHQL(findTheLabourProtectionSQL(cdt));
        }catch(Exception e){
            log.error(e.getMessage());
            e.printStackTrace();
            return null;
        }
        
    }
    public String pageInitFindTheLabourProtection(MaLabourCdt cdt, String roll){
        try{
            return pageFind(roll,findTheLabourProtectionSQL(cdt));
        }catch(Exception e){
            log.error(e.getMessage());
            e.printStackTrace();
            return null;
        }
        
    }

    public Boolean testTheLabourProtection(Ma劳保台帐 pmp){
        boolean flag=false;
        List<Ma劳保台帐明细> mxlList=new ArrayList<Ma劳保台帐明细>();
        mxlList=pmp.getMxList();
        for(Ma劳保台帐明细 mx:mxlList){
            String sql1="select * from MA_劳保策略  a  where a.工种ID =";
            String sqlString2="select top 1 * from MA_劳保台帐明细  a,MA_劳保台帐  b where b.领用单位ID='"+pmp.get领用单位id()+"' " +
            		"and a.工种ID = '"+mx.get工种id()+"' order by a.录入时间";
        }
        return null;
    }

    public Boolean updateTheLabourProtection(Ma劳保台帐 pmp){
        try {
            return maPlanTrans.serviceUpdateTheLabourProtection(pmp);
            } catch (Exception e) {
                log.error(e.getMessage());
                e.printStackTrace();
                return false;
            }
    }


    public Boolean deleteTheLabourProtection(String id){
        try {
            return maPlanTrans.serviceDeleteTheLabourProtection(id);
            } catch (Exception e) {
                log.error(e.getMessage());
                e.printStackTrace();
                return false;
            }
    }

    public Ma劳保台帐 findTheLabourProtectionById(String id){
        try{
            Ma劳保台帐 tz=new Ma劳保台帐();
            String sqlString="from Ma劳保台帐明细  a where a.劳保台帐id='"+id+"'";
            tz=(Ma劳保台帐)service.findObj(Ma劳保台帐.class, id);
            tz.setMxList(service.findObjsHQL(sqlString));
            return tz;
        }catch(Exception e){
            log.error(e.getMessage());
            e.printStackTrace();
            return null;
        }
        
    }

	public String getThePurchaseOrderInquires(String oid,String roll) {
		try {
			StringBuffer sql=new StringBuffer();
			if(oid==null){
				return null;
			}
			String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
			if("SQLSERVER".equals(dataBaseType)){
			        //数据库为sqlserver时执行的操作
			    sql.append("select a.申请单位,convert(varchar(10),a.申请时间,120) 申请日期, isnull(c.申请数量,0.0) 申请数量,isnull(c.批准数量,0.0) 批准数量 from (select 采购计划ID,物资分类ID,规格型号ID from MA_物资计划明细"); 
	            sql.append(" where OID='").append(oid).append("') b ");
	            sql.append(" left join MA_物资计划 a on b.采购计划ID=a.关联计划ID left join MA_物资计划明细 c on a.OID=c.采购计划ID where b.规格型号ID=c.规格型号ID and b.物资分类ID=c.物资分类ID");
			}else if("ORACLE".equals(dataBaseType)){
			        //数据库为oracle时执行的操作
			    sql.append("select a.申请单位,to_char(a.申请时间,'yyyy-mm') 申请日期, nvl(c.申请数量,0.0) 申请数量,nvl(c.批准数量,0.0) 批准数量 from (select 采购计划ID,物资分类ID,规格型号ID from MA_物资计划明细"); 
	            sql.append(" where OID='").append(oid).append("') b ");
	            sql.append(" left join MA_物资计划 a on b.采购计划ID=a.关联计划ID left join MA_物资计划明细 c on a.OID=c.采购计划ID where b.规格型号ID=c.规格型号ID and b.物资分类ID=c.物资分类ID");
			}
			return pageFind(roll,sql.toString(),new HashMap(),4,MaPurchaseOrderVo.class);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return null;
		}
	}

	public String pageInitPurchaseOrderInquires(MaPurchaseOrderCdt cdt,
			String roll) {
		try {
		    StringBuffer sql=new StringBuffer();
		    String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
		    if("SQLSERVER".equals(dataBaseType)){
		            //数据库为sqlserver时执行的操作
		        sql.append("select convert(varchar(32),p.OID) oid,convert(varchar(32),采购计划ID) 采购计划id,CONVERT(varchar(32),物资分类ID) 物资分类id,分类编码,物资分类,单位,规格型号,规格流水号,convert(varchar(32),厂商ID) 厂商id,厂商名称,采购权限,计划单价,采购单价,申请数量,批准数量,金额,明细类型,p.备注,convert(varchar(32),p.录入人ID) 录入人id,录入人,p.录入时间,技术参数,用途,convert(varchar(32),规格型号ID) 规格型号id,库存量,领用状态 ,未领数量,上月余量,购买状态,未购数量,上月未购 from MA_物资计划明细 as p left join MA_物资计划 b on p.采购计划ID=b.OID where b.分类 = '采购计划' ");
		    }else if("ORACLE".equals(dataBaseType)){
		            //数据库为oracle时执行的操作
		        sql.append("select to_char(p.OID) \"oid\",to_char(采购计划ID) \"采购计划id\",to_char(物资分类ID) \"物资分类id\",分类编码,物资分类,单位,规格型号,规格流水号,to_char(厂商ID) \"厂商id\",厂商名称,采购权限,计划单价,采购单价,申请数量,批准数量,金额,明细类型,p.备注,to_char(p.录入人ID) \"录入人id\",录入人,p.录入时间,技术参数,用途,to_char(规格型号ID) \"规格型号id\",库存量,领用状态 ,未领数量,上月余量,购买状态,未购数量,上月未购 from MA_物资计划明细 p left join MA_物资计划 b on p.采购计划ID=b.OID where b.分类 = '采购计划' ");
		    }
			if(cdt!=null){
	            if(!StringUtil.isEmpty(cdt.getMaType())){
	            	String type=cdt.getMaType().replaceAll(",", "','");
	                sql.append(" and (p.物资分类 in ('"+type+"') or b.类型分类名 in ('"+type+"')) ");
	            }
	            if(!StringUtil.isEmpty(cdt.getStart())){
	                if("SQLSERVER".equals(dataBaseType)){
	                    //数据库为sqlserver时执行的操作
	                    sql.append(" and b.申请时间 >= '"+cdt.getStart()+" 00:00:00' ");
    	            }else if("ORACLE".equals(dataBaseType)){
    	                    //数据库为oracle时执行的操作
    	                sql.append(" and b.申请时间 >=to_date( '"+cdt.getStart()+" 00:00:00','yyyy-mm-dd hh24:mi:ss') ");
    	            }
	            }
	            if(!StringUtil.isEmpty(cdt.getEnd())){
	                if("SQLSERVER".equals(dataBaseType)){
	                    //数据库为sqlserver时执行的操作
	                    sql.append(" and b.申请时间 <= '"+cdt.getEnd()+" 23:59:59' ");
    	            }else if("ORACLE".equals(dataBaseType)){
    	                    //数据库为oracle时执行的操作
    	                sql.append(" and b.申请时间 <=to_date( '"+cdt.getEnd()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
    	            }
	            }
	            if(!StringUtil.isEmpty(cdt.getBuystate())){
	            	sql.append(" and (p.购买状态 is null or p.购买状态='"+cdt.getBuystate()+"') ");
	            }
	        }
			return pageFind(roll,sql.toString(),new HashMap(),4,Ma物资计划明细.class);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return null;
		}
	}

	public List<Ma物资计划明细> getPurchasePlanPrice(List<Ma物资计划明细> pl,String unitId,String type) {
		List<Ma物资计划明细> jh=new ArrayList<Ma物资计划明细>();
		try {
			List<Ma帐套> list= service.findObjsHQL("from Ma帐套 a where a.单位id='"+unitId+"' and a.类型分类名='"+type+"' and a.类型='实体库'");
			Ma帐套 zt=list.isEmpty()?null:list.get(0);
			if(zt==null){
				return null;
			}
			
			for(Ma物资计划明细 p:pl){
				String sql="from Ma物资库存 a where a.帐套id='"+zt.getOid()+"' and a.规格型号id='"+p.get规格型号id()+"'";
				List<Ma物资库存> kc=service.findObjsHQL(sql);
				if(kc!=null&&kc.size()>0){
				p.set计划单价(kc.get(0).get单价());
				p.set计划单价(null);
				}
				jh.add(p);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return jh;
	}
    

}
